The term database applies to any system in which information is categorized, stored, and used. A database is a collection of related information that is grouped as a single item. A simple example of a database is a card file, which contains the name, address, and phone number of multiple individuals. The physical card file is not the database. Rather, the database is the contents of the card file the specific manner in which the contents are organized. The physical card file is only a tool for organizing the information. In this regard, computer databases, such as MICROSOFT ACCESS and DBASE are tools for storing and organizing large quantities of information.
Information in a database is typically organized in and stored in a table of rows and columns. Rows in a database file are known as “records,” and columns are known as “fields.” Referring back to the example of a card file, each card in the card file is a single record, and each category of information on the card is a field. Fields can contain any type of information that can be categorizes. In the example, each “card” may contain three fields: name, address, and phone number of an individual. Because each card contains the same type of information, the collection of individual cards makes a database.
Because databases are stored and organized as a table of rows and columns, computer spreadsheet programs are a logical choice for storing databases. However, spreadsheet programs are limited to the number of cells that can contain data, which makes spreadsheet programs impractical for storing large databases. On the other hand, the fact that spreadsheet programs manipulate and store data in a tabular format makes spreadsheet programs suitable for managing small databases. In fact, most users of spreadsheet programs often use a spreadsheet to manage simple databases, such as contact lists and phone lists.
However, spreadsheet programs do not link the data in individual cells together in any way other than the fact that the individual data elements are located next to each other. For instance, two adjacent cells in a row would not be considered to be related or logically linked together, and may easily become separated. This inability of spreadsheet programs to logically associate adjacent cells with one another leads to several drawbacks for using a spreadsheet program for managing databases. For instance, a user cannot insert any blank records in the database because the spreadsheet program would interpret the blank record as the end of the database. Any records occurring after the blank record would be ignored.
However, the biggest problem encountered using the spreadsheet program as a database manager occurs when the user assumes that a tabular structure exists and therefore the data will behave in a certain way. Unfortunately, the data behaves in a manner opposite of what the user expected. For example, spreadsheet users have been known to get less than desirable results from a sort operation because sorting involves rearranging large amounts of data. Because the individual cells in a record were not logically linked together or associated with one another, when a user attempted to sort the data there is the possibility that the user will make a selection that causes the spreadsheet program to sort the data in a way that the user did not expect. To illustrate, if a user selected only a portion of the data before they invoked the Sort command, the Sort command would cause only the selected data to be sorted. The remaining data would be unaffected. In many instances would give the user a result different that what the user expected. As an illustration, suppose the user wanted to sort a phone list, which included columns labeled “NAME,” “ADDRESS,” and “PHONE NUMBER” using the “NAME” column. Intuitively, the user would likely select the “NAME” columns and invoke the Sort command and expect that the phone list would be sorted alphabetically by name. However, if the user selected only the column labeled “NAME” and invoked the Sort command from within the spreadsheet program, only the column labeled “NAME” would be sorted. The remaining columns, namely “ADDRESS” and “PHONE NUMBER” would be unchanged. The result of this simple sorting action would be to completely jumble the data and put unrelated cells next to one another.
One solution to the problem of sorting data was to have the user define the database every time they wished to rearrange the data. In previous versions of the MICROSOFT EXCEL spreadsheet program for the Macintosh computer, “intelligence” was added to the spreadsheet program in an attempt to overcome this problem. For example, when the user invoked the Sort command, the MICROSOFT EXCEL spreadsheet program would “intelligently” identify associated columns of data and automatically select them. However, if the any of the columns included a blank record, or row, the “intelligent” selection would select only those contiguous records between the field names in the top-most row and the blank record. If any data was present below the blank record, it was be excluded by the “intelligent” selection. Therefore, when the Sort command was invoked, only data above the blank record would be sorted. Unfortunately, this garbled data and resulted in user frustration.
Another “intelligent” solution incorporated into previous versions of the MICROSOFT EXCEL spreadsheet program for the Macintosh was the propagation of the table format to newly added rows and columns to an existing table. Under this solution, every time a record was added, the record was given the same format as the record above it. Unfortunately, both of these solutions suffered from a common flaw: adding intelligent solutions cannot account for every possible data layout. If one were to try to invent an “intelligent” solution for every problem, the program would be too cumbersome to operate effectively. Furthermore, one solution that works for one user may not work for another user. Thus, the amount of guesswork to build in acceptable “intelligent” solutions must be kept to a minimum, or the program runs the risk of doing too much unwanted work for the user.
Thus, there is a general need in the art for a more efficient method of creating an embedded database, or List Object, in a spreadsheet application so that the contents of the List Object act as the user expects when the user manipulates the data. There is a still a further need in the art for a method of allowing a user to graphically create a List Object within a spreadsheet program.